package DAO;

import java.sql.*;
import java.util.*;

import javax.naming.*;
import javax.sql.*;

import model.Board;

public class BoardDAO {
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;
	
	public BoardDAO(){
		try {
			Context init = new InitialContext();
			DataSource ds = (DataSource)init.lookup("java:comp/env/jdbc/mysql");
			con = ds.getConnection();
		} catch (Exception e) {
			System.err.println("DB연결 실패");
			e.printStackTrace();
		}
	}
	
	
	
	
	//////////////////////////////테스트용 메서드들/////////////////////////////////
	public static void getBoardList(){
		BoardDAO boardDAO = new BoardDAO();
		List<Board> list = boardDAO.getBoardList(1, 20);
		for(Board board : list){
			System.out.println(board);
		}
	}
	
	public static void getListCountTest(){
		BoardDAO boardDAO = new BoardDAO();
		int outcome = boardDAO.getListCount();
		System.out.println("getListCountTest결과 :" + outcome);
	}
	
	public static void insertTest(){
		//test용
		Board testBoard = new Board(0,"kim","1","subject1","content1","file1",0,0,0,0,null);
		BoardDAO boardDAO = new BoardDAO();
		boolean res = boardDAO.insert(testBoard);
		System.out.println("결과:"+res);
	}
	
	public static void deleteTest(int num){
		BoardDAO boardDAO = new BoardDAO();
		if(boardDAO.delete(num))
			System.out.println(num + "번 글 delete 테스트 성공");				
		else
			System.out.println("delete 테스트 실패");
		
	}
	
	public static void replyTest(){
		Board testBoard = new Board(0,"park","r","Rsubject","Rcontent","Rfile",0,0,0,0,null);
		int num = new BoardDAO().reply(testBoard);
		System.out.println(num+ "번 답글을 달았습니다.");
		
		
	}
	
	
	
	////////////////////////////////////////////////////////////////////
	
	
	//글의 개수 구하기
	public int getListCount(){
		int x=-1;

		try {
			pstmt = con.prepareStatement("select count(*) from board");
			rs = pstmt.executeQuery();
			
			if(rs.next())
				x = rs.getInt(1);
		}
		catch (Exception e) {
			System.err.println("BoardDAO.getListCount() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}


		return x;
	}
		
	//list 가져오기
	public List<Board> getBoardList(int page, int limit){
		StringBuilder sql = new StringBuilder();
		sql.append("select * from board order by re_ref desc, re_seq asc");
		sql.append(" limit ?,?");
		List<Board> list = new ArrayList<Board>();
		
		int startrow = (page-1)*10; //읽기 시작할 번호
		int endrow = startrow + limit-1; //읽을 마지막 번호

		try {
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs = pstmt.executeQuery();
			System.out.println("select 성공");
			while(rs.next()){
				Board board = new Board();
				board.setNum(rs.getInt("num"));
				board.setName(rs.getString("name"));
				board.setPass(rs.getString("pass"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setFile(rs.getString("file"));
				board.setRe_ref(rs.getInt("re_ref"));
				board.setRe_lev(rs.getInt("re_lev"));
				board.setRe_seq(rs.getInt("re_seq"));
				board.setReadcount(rs.getInt("readcount"));
				board.setRegdate(rs.getDate("regdate"));
				list.add(board);
			}
		}
		catch (Exception e) {
			System.err.println("BoardDAO.getList() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return list;
	}
	
	//마지막에 저장한 글번호 가져오기
	public int getRecentNumber(){
		int n = 0;
		try {
			String sql = "select ifnull(max(num),0) from board";
			rs = con.createStatement().executeQuery(sql);
			if(rs.next()){
				return rs.getInt(1);
			}
			System.out.println("최근 글번호 가져오기 체크 요망");
		}
		catch (Exception e) {
			System.err.println("BoardDAO.getRecentNumber() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return -1;
	}
	
	//글 내용 보기
	public Board getDetail(int num){
		Board board=null;
		String sql = "select * from board where num=?";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1,num);
			rs=pstmt.executeQuery();
			
			if(rs.next()){
				board = new Board();
				board.setNum(rs.getInt("num"));
				board.setName(rs.getString("name"));
				board.setPass(rs.getString("pass"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setFile(rs.getString("file"));
				board.setRe_ref(rs.getInt("re_ref"));
				board.setRe_lev(rs.getInt("re_lev"));
				board.setRe_seq(rs.getInt("re_seq"));
				board.setReadcount(rs.getInt("readcount"));
				board.setRegdate(rs.getDate("regdate"));				
			}
			return board;
		}
		catch (Exception e) {
			System.err.println("BoardDAO.getDetail() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		
		return null;
	}
	
	
	//글 등록
	public boolean insert(Board board){
		StringBuilder sql=new StringBuilder();
		sql.append("insert into board(name,pass,subject,content,file,re_ref,re_lev,re_seq,readcount,regdate)");
		sql.append(" values(?,?,?,?,?,0,0,0,0,sysdate())");
		int result=-1;
		
		try {
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setString(1, board.getName());
			pstmt.setString(2, board.getPass());
			pstmt.setString(3, board.getSubject());
			pstmt.setString(4, board.getContent());
			pstmt.setString(5, board.getFile());
			
			result = pstmt.executeUpdate();
			if(result==-1){
				System.err.println("BoardDAO.insert() insert가 되지 않음");
				return false;				
			}
			System.err.println("BoardDAO.insert() insert가 성공 "+board);
			pstmt.close();
			
			
			//num번호 알아오기
			int recentNum = getRecentNumber();
			//re_ref와 num값을 일치시키기
			String update = "update board set re_ref=? where num=?";
			pstmt = con.prepareStatement(update);
			
			//글과 답글의 경우 분리
			if(board.getNum()<=0)
				pstmt.setInt(1, recentNum); //일반 글일 경우
			else
				pstmt.setInt(1, board.getNum()); //답글일 경우
			pstmt.setInt(2, recentNum);
			result=-2;
			result = pstmt.executeUpdate();
			if(result==-2){
				System.out.println("re_ref의 업데이트 실패");
				return false;
			}
			System.out.println("update 성공");
			return true;
			
		}
		catch (SQLException e) {
			System.err.println("BoardDAO.insert() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		
		return false;
	}
	
	//글 수정
	public boolean modify(Board modifyBoard){
		String sql = "update board set subject=?, content=? where num=?";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, modifyBoard.getSubject());
			pstmt.setString(2, modifyBoard.getContent());
			pstmt.setInt(3, modifyBoard.getNum());
			
			int outcome=-1;
			outcome=pstmt.executeUpdate();
			if(outcome==-1)
				System.out.println("modify 실패");
			System.out.println("modify 성공");
			System.out.println(outcome + "개의 행이 업데이트 되었습니다.");
			return true;
		}
		catch (Exception e) {
			System.err.println("BoardDAO.modify() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return false;
	}
	
	//글 삭제
	public boolean delete(int num){
		try {
			String sql = "delete from board where num=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, num);
			System.out.println(num + "번 글 삭제 예정");
			int outcome = pstmt.executeUpdate();
			if(outcome==-1){
				System.out.println("delete 체크 요망");
				return false;
			}
			else if(outcome==0){
				System.out.println(num + "번 글이 지워지지 않음");
				return false;
			}
			else
				System.out.println(num + "번 글 삭제 완료");
		}
		catch (Exception e) {
			System.err.println("BoardDAO.delete() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return true;
	}
	
	//글 답변
	public int reply(Board board){
		StringBuilder sql = new StringBuilder();
		
		try {
			sql.append("update board set re_seq=re_seq+1");
			sql.append(" where re_ref=? and re_seq>?");
			pstmt = con.prepareStatement(sql.toString());
			pstmt.setInt(1, board.getRe_ref());
			pstmt.setInt(2, board.getRe_seq());
			
			if(pstmt.executeUpdate()==0)
				System.out.println("update된 행 없음");
			
			
			//insert 메서드를 호출해서 다변을 insert시킴
			board.setRe_seq(board.getRe_seq()+1);
			board.setRe_lev(board.getRe_lev()+1);
			if(insert(board)){
				System.out.println("답변 달기 성공");
				return getRecentNumber();
			}
		}
		catch (Exception e) {
			System.err.println("BoardDAO.reply() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return -1;
	}
	
	//조회수 업데이트 p389
	public void setReadCountUpdate(int num){
		String sql = "update board set readcount=readcount+1 where num=num=" + num;
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
		}
		catch (Exception e) {
			System.err.println("BoardDAO.setReadcountUpdate() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
	}
	
	//글쓴이인지 확인 p390
	public boolean isBoardWriter(int num, String pass){
		String sql = "select * from board where num=?";
		
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, num);
			rs = pstmt.executeQuery();
			if(rs.next()){
				if(pass.equals(rs.getString("pass")))
					return true;
				else
					System.out.println("비번이 틀립니다.");
			}
		}
		catch (Exception e) {
			System.err.println("BoardDAO.isBoardWriter() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return false;
	}
	
	
	public List getList(){
		try {
			
		}
		catch (Exception e) {
			System.err.println("BoardDAO.insert() 에러발생");
			e.printStackTrace();
		}
		finally{
			if(rs!=null)
				try{rs.close();}catch(SQLException e){}
			if(pstmt!=null)
				try{pstmt.close();}catch(SQLException e){}
		}
		return null;
	}
}
